package com.bjsdzk.common.excel;

import com.bjsdzk.common.core.ServiceException;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * Created by zouLu on 2017-12-14.
 */
public class ExportExcelUtils {

    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        fileName = new String(fileName.getBytes(), "iso8859-1");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        exportExcel(data, response.getOutputStream());
    }

    public static <T> void exportExcel(ExcelData<T> data, OutputStream out) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);

            wb.write(out);
        } finally {
            wb.close();
        }
    }

    private static <T> void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData<T> data) {

        int rowIndex = 0;
        EntityHandler<T> entityHandler = data.getEntityHandler();
        List<List<Object>> rows = entityHandler.transfer(data.getRows());
        rowIndex = writeTitlesToExcel(wb, sheet, data);
        writeRowsToExcel(wb, sheet, rows, rowIndex);
        autoSizeColumns(sheet, data.getTitleMap().size() + 1);

    }

    private static <T> int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, ExcelData<T> data) {
        int rowIndex = 0;
        int colIndex = 0;

        Font titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        // titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);

        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        Map<Integer, List<ExcelTitleItem>> titleMap = data.getTitleMap();
        int initialColIndex = 0;

        for (Integer key : titleMap.keySet()) {
            Row titleRow = sheet.createRow(rowIndex);
            // titleRow.setHeightInPoints(25);
            colIndex = initialColIndex;
            initialColIndex = 0;
            List<ExcelTitleItem> excelTitleItemList = titleMap.get(key);
            int preIndex = 0;
            for (ExcelTitleItem excelTitleItem : excelTitleItemList) {
                int titleOrder = excelTitleItem.getTitleOrder();
                int high = excelTitleItem.getHigh();
                int infoOrder = excelTitleItem.getInfoOrder();
                int size = excelTitleItem.getSize();

                Cell cell = titleRow.createCell(colIndex);
                boolean format = excelTitleItem.isFormat();
                String value = excelTitleItem.getValue();
                if (format) {
                    value = ExcelFormatUtils.format(data.getTitleHandlerMap(), value);
                }
                cell.setCellValue(value);
                if (size > 1 || high > 1) {
                    if (high > 1) {
                        initialColIndex += size;
                    }
                    CellRangeAddress region =
                            new CellRangeAddress(titleOrder
                                    , titleOrder + (high - 1)
                                    , preIndex
                                    , size + preIndex - 1);
                    sheet.addMergedRegion(region);
                    setBorderStyle(XSSFCellStyle.BORDER_THIN, region, sheet, wb);
                }
                cell.setCellStyle(titleStyle);

                preIndex = infoOrder + size;

                colIndex += size;
            }
            rowIndex++;
        }

        return rowIndex;
    }

    public static void setBorderStyle(int border, CellRangeAddress region, Sheet sheet, XSSFWorkbook wb) {
        RegionUtil.setBorderBottom(border, region, sheet, wb);
        RegionUtil.setBorderLeft(border, region, sheet, wb);
        RegionUtil.setBorderRight(border, region, sheet, wb);
        RegionUtil.setBorderTop(border, region, sheet, wb);
    }

    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;

        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        // dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));

        for (List<Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            // dataRow.setHeightInPoints(25);
            colIndex = 0;

            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }

//            for (int i = 0; i < rowData.size(); i++) {
//                sheet.autoSizeColumn(i);
//            }

            rowIndex++;
        }

        return rowIndex;
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {

        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(BorderSide.TOP, color);
        style.setBorderColor(BorderSide.LEFT, color);
        style.setBorderColor(BorderSide.RIGHT, color);
        style.setBorderColor(BorderSide.BOTTOM, color);
    }

    public static Map<Integer, List<ExcelTitleItem>> buildTitleMap(List<ExcelTitleItem> excelTitleItemList) {
        Map<Integer, List<ExcelTitleItem>> titleMap = new LinkedHashMap<>();
        for (ExcelTitleItem excelTitleItem : excelTitleItemList) {
            int titleOrder = excelTitleItem.getTitleOrder();
            if (titleMap.containsKey(titleOrder)) {
                titleMap.get(titleOrder).add(excelTitleItem);
            } else {
                List<ExcelTitleItem> tmpList = new ArrayList<>();
                tmpList.add(excelTitleItem);
                titleMap.put(excelTitleItem.getTitleOrder(), tmpList);
            }
        }

        return titleMap;
    }
}

